using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;

namespace CodeGenerator.Classes
{
    public class GeneratorAjaxHandler
    {
        private string mTableName = "";
        private string connStr = "";
        public static string NameSpace;


        public GeneratorAjaxHandler(string connectionString, string TableName)
        {
            mTableName = TableName;
            connStr = connectionString;
        }

 


        #region AJAXHANDLER

        public static string PropCaseStartLower(string strText)
        {
            string retVal = PropCase(strText);
            retVal = retVal.Substring(0, 1).ToLower() + retVal.Substring(1, (retVal.Length - 1));
            return retVal;
        }

        public static string PropCase(string strText)
        {
            return new CultureInfo("en").TextInfo.ToTitleCase(strText.ToLower());
        }

        public string ReturnClassAjaxHandler()
        {

            StringBuilder ret = new StringBuilder();
            ret.AppendLine("<%@ WebHandler Language="+"\""+"C#"+"\""+" Class="+"\""+NameSpace+"."+PropCaseStartLower(mTableName)+"\""+" %>");

            ret.AppendLine("using System;");
            ret.AppendLine("using System.Collections.Generic;");
            ret.AppendLine("using System.Linq;");
            ret.AppendLine("using System.Web;");
            ret.AppendLine("using System.Configuration;");
            ret.AppendLine("using RTGS.Library;");
            ret.AppendLine("using RTGS.Library.DataAccess.UserManagement;");
            ret.AppendLine("using RTGS.Library.Entities.UserManagement;");
            ret.AppendLine("using RTGS.Library.Enum;");
            ret.AppendLine("using RTGS.Web.support;");
            ret.AppendLine("namespace " + NameSpace);
            ret.AppendLine("\t {");
            ret.AppendLine("\t\tpublic class " + PropCaseStartLower(mTableName) + " : BaseHandler");
            ret.AppendLine("\t \t{");
            ret.AppendLine("\t\t  private " + PropCaseStartLower(mTableName).Replace("_", "") + "Repo " + PropCaseStartLower(mTableName.Replace("_", "")) + "Dal = new " + PropCaseStartLower(mTableName).Replace("_", "") + "Repo(ConfigurationManager.ConnectionStrings[" + "\"" + "Rtgs" + "\"" + "].ConnectionString);");
            ret.AppendLine("\t\t  private sysTLogRepo logDal = new sysTLogRepo(ConfigurationManager.ConnectionStrings[" + "\"" + "Rtgs" + "\"" + "].ConnectionString);");
            ret.AppendLine("\t\t  private sysTLog log = new sysTLog();");
            ret.AppendLine("\t\t  private string MethodName = string.Empty;");
            ret.AppendLine("\t\t  public override void ProcessRequest(HttpContext context)");
            ret.AppendLine("\t\t\t{");
            ret.AppendLine("\t\t\t\tbase.ProcessRequest(context);");
            ret.AppendLine("\t\t\t\tMethodName = context.Request.Params[" + "\"" + "method" + "\"" + "];");
            ret.AppendLine("\t\t\t\tcontext.Response.Clear();");
            ret.AppendLine("\t\t\t\tcontext.Response.Buffer = true;");
            ret.AppendLine("\t\t\t\tswitch (MethodName.ToLower())");
            ret.AppendLine("\t\t\t\t{");
            ret.AppendLine("\t\t\t\t\tcase (" + "\"" + "insert" + "\"" + "):");
            ret.AppendLine("\t\t\t\t\tcontext.Response.Write(Insert(context));");
            ret.AppendLine("\t\t\t\t\tbreak;");
            ret.AppendLine("\t\t\t\t\tcase (" + "\"" + "update" + "\"" + "):");
            ret.AppendLine("\t\t\t\t\tcontext.Response.Write(Update(context));");
            ret.AppendLine("\t\t\t\t\tbreak;");
            ret.AppendLine("\t\t\t\t\tcase (" + "\"" + "delete" + "\"" + "):");
            ret.AppendLine("\t\t\t\t\tcontext.Response.Write(Delete(context));");
            ret.AppendLine("\t\t\t\t\tbreak;");
            ret.AppendLine("\t\t\t\t\tcase (" + "\"" + "getdata" + "\"" + "):");
            ret.AppendLine("\t\t\t\t\tcontext.Response.Write(GetData(context));");
            ret.AppendLine("\t\t\t\t\tbreak;");
            ret.AppendLine("\t\t\t\t\tdefault:");
            ret.AppendLine("\t\t\t\t\t// do nothing");
            ret.AppendLine("\t\t\t\t\tbreak;");
            ret.AppendLine("\t\t\t\t}");
            ret.AppendLine("\t\t\t\tcontext.Response.Flush();");
            ret.AppendLine("\t\t\t\tcontext.Response.End();");
            ret.AppendLine("\t\t\t}");
            ret.AppendLine("\n");
            ret.AppendLine("\t\t public override bool IsReusable");
            ret.AppendLine("\t\t\t{");
            ret.AppendLine("\t\t\t\tget");
            ret.AppendLine("\t\t\t\t{");
            ret.AppendLine("\t\t\t\t\treturn false;");
            ret.AppendLine("\t\t\t\t}");
            ret.AppendLine("\t\t\t}");
            ret.AppendLine("\n");
            ret.AppendLine("\n");
            ret.AppendLine("#region CRUD Handler");
            ret.AppendLine("\n");
            ret.AppendLine(CreateInsertAjaxHandler());
            ret.AppendLine("\n");
            ret.AppendLine(CreateUpdateAjaxHandler());
            ret.AppendLine("\n");
            ret.AppendLine(CreateDeleteAjaxHandler());
            ret.AppendLine("\n");
            ret.AppendLine(CreateGetDataAjaxHandler());
            ret.AppendLine("#endregion");
            ret.AppendLine("\t\t}");
            ret.AppendLine("\t}");


            return ret.ToString();
        }

        private string CreateInsertAjaxHandler()
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("private string Insert(HttpContext context)");
            sql.AppendLine("\t{");
            sql.AppendLine("\t\tJSONHelper helper = new JSONHelper();");
            sql.AppendLine("\t\t" + PropCaseStartLower(mTableName).Replace("_", "") + " obj = new " + PropCaseStartLower(mTableName).Replace("_", "") + "();");

            string ret = "";
            string dataType = string.Empty;

            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand("SELECT Column_Name,Data_Type From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
            SqlDataReader rdr;

            try
            {
                conn.Open();
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    dataType = rdr[1].ToString();
                    switch (dataType.Trim().ToLower())
                    {
                        case "varchar":
                            //dataType = "string";
                            dataType = "Convert.ToString(";
                            break;

                        case "decimal":
                            dataType = "Convert.ToDecimal(";
                            break;


                        case "datetime":
                            dataType = "Convert.ToDateTime(";
                            break;

                        case "char":
                            dataType = "Convert.ToString(";
                            break;

                        case "text":
                            dataType = "Convert.ToString(";
                            break;

                        case "numeric":
                            dataType = "Convert.ToInt32(";
                            break;

                        case "int":
                            dataType = "Convert.ToInt32(";
                            break;

                        default:
                            dataType = "Convert.ToString(";
                            break;
                    }

                    sql.AppendLine("\t\tobj." + PropCaseStartLower(rdr[0].ToString()).Replace("_", "") + " = " + dataType + "context.Request.Params[" + "\"" + PropCaseStartLower(rdr[0].ToString()).Replace("_", "") + "\"" + "]);");

                }

                sql.AppendLine("\n");
                sql.AppendLine("\t\tif (" + PropCaseStartLower(mTableName.Replace("_", "")) + "Dal.Save(obj))");
                sql.AppendLine("\t\t\t{");
                sql.AppendLine("\t\t\t\thelper.Data = obj;");
                sql.AppendLine("\t\t\t\thelper.Message = " + "\"" + "Data has been saved!" + "\"" + ";");
                sql.AppendLine("\t\t\t\thelper.IsSuccess = true;");
                sql.AppendLine("\t\t\t\tlog = new sysTLog();");
                sql.AppendLine("\t\t\t\tlog.logEvent = " + "\"" + "Insert " + PropCaseStartLower(mTableName).Replace("_", "") + "Success" + "\"" + ";");
                sql.AppendLine("\t\t\t\tlog.logMessage = " + PropCaseStartLower(mTableName.Replace("_", "")) + "Dal.GetErrorMessage();");
                sql.AppendLine("\t\t\t}");
                sql.AppendLine("\t\telse");
                sql.AppendLine("\t\t\t{");
                sql.AppendLine("\t\t\t\thelper.IsSuccess = false;");
                sql.AppendLine("\t\t\t\thelper.Message = " + PropCaseStartLower(mTableName.Replace("_", "")) + "Dal.GetErrorMessage();");
                sql.AppendLine("\t\t\t\tlog = new sysTLog();");
                sql.AppendLine("\t\t\t\tlog.logEvent = " + "\"" + "Insert " + PropCaseStartLower(mTableName).Replace("_", "") + "Error" + "\"" + ";");
                sql.AppendLine("\t\t\t\tlog.logMessage = " + PropCaseStartLower(mTableName.Replace("_", "")) + "Dal.GetErrorMessage();");
                sql.AppendLine("\t\t\t}");
                sql.AppendLine("\n");
                sql.AppendLine("\t\t\t\tlog.logSource = " + "\"" + PropCaseStartLower(mTableName) + ".ashx" + "\"" + ";");
                sql.AppendLine("\t\t\t\tlog.createdBy = base.UserLoggedIn;");
                sql.AppendLine("\t\t\t\tlog.createdDate = DateTime.Now;");
                sql.AppendLine("\t\t\t\tlogDal.Save(log);");
                sql.AppendLine("\t\t\t\treturn Newtonsoft.Json.JsonConvert.SerializeObject(helper);");
                sql.AppendLine("\t}");

            }

            catch
            { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
                cmd.Dispose();
            }


            return sql.ToString();
        }

        private string CreateUpdateAjaxHandler()
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("private string Update(HttpContext context)");
            sql.AppendLine("\t{");
            sql.AppendLine("\t\tJSONHelper helper = new JSONHelper();");
            sql.AppendLine("\t\t" + PropCaseStartLower(mTableName).Replace("_", "") + " obj = new " + PropCaseStartLower(mTableName).Replace("_", "") + "();");

            string ret = "";
            string dataType = string.Empty;

            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand("SELECT Column_Name,Data_Type From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
            SqlDataReader rdr;

            try
            {
                conn.Open();
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    dataType = rdr[1].ToString();
                    switch (dataType.Trim().ToLower())
                    {
                        case "varchar":
                            dataType = "Convert.ToString(";
                            break;

                        case "decimal":
                            dataType = "Convert.ToDecimal(";
                            break;


                        case "datetime":
                            dataType = "Convert.ToDateTime(";
                            break;

                        case "char":
                            dataType = "Convert.ToString(";
                            break;

                        case "text":
                            dataType = "Convert.ToString(";
                            break;

                        case "numeric":
                            dataType = "Convert.ToInt32(";
                            break;

                        case "int":
                            dataType = "Convert.ToInt32(";
                            break;

                        default:
                            dataType = "Convert.ToString(";
                            break;
                    }

                    sql.AppendLine("\t\tobj." + PropCaseStartLower(rdr[0].ToString()).Replace("_", "") + " = " + dataType + "context.Request.Params[" + "\"" + PropCaseStartLower(rdr[0].ToString()).Replace("_", "") + "\"" + "]);");

                }

                sql.AppendLine("\n");
                sql.AppendLine("\t\tif (" + PropCaseStartLower(mTableName.Replace("_", "")) + "Dal.Update(obj))");
                sql.AppendLine("\t\t\t{");
                sql.AppendLine("\t\t\t\thelper.Data = obj;");
                sql.AppendLine("\t\t\t\thelper.Message = " + "\"" + "Data has been saved!" + "\"" + ";");
                sql.AppendLine("\t\t\t\thelper.IsSuccess = true;");
                sql.AppendLine("\t\t\t\tlog = new sysTLog();");
                sql.AppendLine("\t\t\t\tlog.logEvent = " + "\"" + "Update " + PropCaseStartLower(mTableName).Replace("_", "") + "Success" + "\"" + ";");
                sql.AppendLine("\t\t\t\tlog.logMessage = " + PropCaseStartLower(mTableName.Replace("_", "")) + "Dal.GetErrorMessage();");
                sql.AppendLine("\t\t\t}");
                sql.AppendLine("\t\telse");
                sql.AppendLine("\t\t\t{");
                sql.AppendLine("\t\t\t\thelper.IsSuccess = false;");
                sql.AppendLine("\t\t\t\thelper.Message = " + PropCaseStartLower(mTableName.Replace("_", "")) + "Dal.GetErrorMessage();");
                sql.AppendLine("\t\t\t\tlog = new sysTLog();");
                sql.AppendLine("\t\t\t\tlog.logEvent = " + "\"" + "Update " + PropCaseStartLower(mTableName).Replace("_", "") + "Error" + "\"" + ";");
                sql.AppendLine("\t\t\t\tlog.logMessage = "+PropCaseStartLower(mTableName.Replace("_", ""))+"Dal.GetErrorMessage();");
                sql.AppendLine("\t\t\t}");
                sql.AppendLine("\n");
                sql.AppendLine("\t\t\t\tlog.logSource = " + "\"" + PropCaseStartLower(mTableName) + ".ashx" + "\"" + ";");
                sql.AppendLine("\t\t\t\tlog.createdBy = base.UserLoggedIn;");
                sql.AppendLine("\t\t\t\tlog.createdDate = DateTime.Now;");
                sql.AppendLine("\t\t\t\tlogDal.Save(log);");
                sql.AppendLine("\t\t\t\treturn Newtonsoft.Json.JsonConvert.SerializeObject(helper);");
                sql.AppendLine("\t}");

            }

            catch
            { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
                cmd.Dispose();
            }


            return sql.ToString();
        }

        private string CreateDeleteAjaxHandler()
        {
            StringBuilder sql = new StringBuilder();
            try
            {
                sql.AppendLine("private string Delete(HttpContext context)");
                sql.AppendLine("\t{");
                sql.AppendLine("\t\tJSONHelper helper = new JSONHelper();");
                sql.AppendLine("\t\tbool isSuccess = true;");
                sql.AppendLine("\t\tstring[] ids = context.Request.Params[" + "\"" + "id" + "\"" + "].Split(',');");
                sql.AppendLine("\t\tforeach (string id in ids)");
                sql.AppendLine("\t\t{");
                sql.AppendLine("\t\tint ID = Convert.ToInt32(id);");
                sql.AppendLine("\t\t\tisSuccess = " + PropCaseStartLower(mTableName.Replace("_", "")) + "Dal.Delete(ID);");
                sql.AppendLine("\t\t\tif (!isSuccess) break;");
                sql.AppendLine("\t\t}");
                sql.AppendLine("\n");
                sql.AppendLine("\t\tif (isSuccess)");
                sql.AppendLine("\t\t\t{");
                sql.AppendLine("\t\t\t\thelper.Message = " + "\"" + "Data has been saved!" + "\"" + ";");
                sql.AppendLine("\t\t\t\thelper.IsSuccess = true;");
                sql.AppendLine("\t\t\t\tlog = new sysTLog();");
                sql.AppendLine("\t\t\t\tlog.logEvent = " + "\"" + "Delete " + PropCaseStartLower(mTableName).Replace("_", "") + "Success" + "\"" + ";");
                sql.AppendLine("\t\t\t\tlog.logMessage = " + PropCaseStartLower(mTableName.Replace("_", "")) + "Dal.GetErrorMessage();");
                sql.AppendLine("\t\t\t}");
                sql.AppendLine("\t\telse");
                sql.AppendLine("\t\t\t{");
                sql.AppendLine("\t\t\t\thelper.IsSuccess = false;");
                sql.AppendLine("\t\t\t\thelper.Message = " + PropCaseStartLower(mTableName.Replace("_", "")) + "Dal.GetErrorMessage();");
                sql.AppendLine("\t\t\t\tlog = new sysTLog();");
                sql.AppendLine("\t\t\t\tlog.logEvent = " + "\"" + "Delete " + PropCaseStartLower(mTableName).Replace("_", "") + "Error" + "\"" + ";");
                sql.AppendLine("\t\t\t\tlog.logMessage = " + PropCaseStartLower(mTableName.Replace("_", "")) + "Dal.GetErrorMessage();");
                sql.AppendLine("\t\t\t}");
                sql.AppendLine("\n");
                sql.AppendLine("\t\t\t\tlog.logSource = " + "\"" + PropCaseStartLower(mTableName) + ".ashx" + "\"" + ";");
                sql.AppendLine("\t\t\t\tlog.createdBy = base.UserLoggedIn;");
                sql.AppendLine("\t\t\t\tlog.createdDate = DateTime.Now;");
                sql.AppendLine("\t\t\t\tlogDal.Save(log);");
                sql.AppendLine("\t\t\t\treturn Newtonsoft.Json.JsonConvert.SerializeObject(helper);");
                sql.AppendLine("\t}");
            }

            catch
            { }



            return sql.ToString();
        }

        private string CreateGetDataAjaxHandler()
        {
            StringBuilder sql = new StringBuilder();
            try
            {
                sql.AppendLine("\tprivate string GetData(HttpContext context)");
                sql.AppendLine("\t\t{");
                sql.AppendLine("\t\t\tint page = 1;");
                sql.AppendLine("\t\t\tint.TryParse(context.Request.QueryString[" + "\"" + "page" + "\"" + "] ??" + "\"" + "1" + "\"" + ", out page);");
                sql.AppendLine("\t\t\tint pageSize = 10;");
                sql.AppendLine("\t\t\tint.TryParse(context.Request.QueryString[" + "\"" + "rows" + "\"" + "] ?? " + "\"" + "10" + "\"" + ", out pageSize);");
                sql.AppendLine("\t\t\tstring sidx = ((context.Request.QueryString[" + "\"" + "sidx" + "\"" + "]) == null ? " + PropCaseStartLower(mTableName).Replace("_", "") + ".Fields."+GetPK(mTableName)+" : context.Request.QueryString[" + "\"" + "sidx" + "\"" + "].ToString());");
                sql.AppendLine("\t\t\tstring sord = ((context.Request.QueryString[" + "\"" + "sord" + "\"" + "]) == null ? " + "\"" + "ASC" + "\"" + " : context.Request.QueryString[" + "\"" + "sord" + "\"" + "].ToString());");
                sql.AppendLine("\t\t\tstring field = ((context.Request.Params[" + "\"" + "field" + "\"" + "]) == null ? string.Empty : context.Request.QueryString[" + "\"" + "field" + "\"" + "].ToString());");
                sql.AppendLine("\t\t\tstring operand = ((context.Request.Params[" + "\"" + "operand" + "\"" + "]) == null ? string.Empty : context.Request.QueryString[" + "\"" + "operand" + "\"" + "].ToString());");
                sql.AppendLine("\t\t\tstring value = ((context.Request.Params[" + "\"" + "value" + "\"" + "]) == null ? string.Empty : context.Request.QueryString[" + "\"" + "value" + "\"" + "].ToString());");
                sql.AppendLine("\t\t\tstring parameters = string.Empty;");
                sql.AppendLine("\n");
                sql.AppendLine("\t\t\tif (!string.IsNullOrEmpty(value))");
                sql.AppendLine("\t\t\t{");
                sql.AppendLine("\t\t\t\tfield = operand.ToLower().Contains(" + "\"" + "like" + "\"" + ") ? " + "\"" + "LOWER(" + "\"" + " + field + " + "\"" + ")" + "\"" + " : field;");
                sql.AppendLine("\t\t\t\tfield = field.ToLower().Contains(" + "\"" + "date" + "\"" + ") ? " + "\"" + "CONVERT(char(8), " + "\"" + " + field + " + "\"" + ", 112)" + "\"" + " : field;");
                sql.AppendLine("\t\t\t\tvar filter = operand.ToLower().Contains(" + "\"" + "like" + "\"" + ") ? " + "\"" + "'%" + "\"" + " + value.ToLower() + " + "\"" + "%'" + "\"" + " :");
                sql.AppendLine("\t\t\t\tfield.ToLower().Contains(" + "\"" + "date" + "\"" + ") ? " + "\"" + "'" + "\"" + " + Helper.ToSQLServerDateISO(value) + " + "\"" + "'" + "\"" + " : " + "\"" + "'" + "\"" + " + value + " + "\"" + "'" + "\"" + ";");
                sql.AppendLine("\t\t\t\tparameters = " + "\"" + "WHERE " + "\"" + " + field + " + "\"" + "\"" + " + operand + " + "\"" + "\"" + " + filter;");
                sql.AppendLine("\t\t\t}");
                sql.AppendLine("\t\t\t\tint totalCount = Convert.ToInt32(" + PropCaseStartLower(mTableName.Replace("_", "")) + "Dal.SelectScalar(SQL.Function.Aggregate.Count, " + PropCaseStartLower(mTableName.Replace("_", "")) + ".Fields.roleId, parameters));");
                sql.AppendLine("\t\t\t\tdecimal dc = decimal.Ceiling(Convert.ToDecimal(totalCount) / Convert.ToDecimal(pageSize));");
                sql.AppendLine("\t\t\t\tint totalPages = Convert.ToInt32(dc);");
                sql.AppendLine("\t\t\t\tList<" + PropCaseStartLower(mTableName).Replace("_", "") + "> pagedList = " + PropCaseStartLower(mTableName.Replace("_", "")) + "Dal.GetList(pageSize, page, sidx, sord, parameters);");
                sql.AppendLine("\t\t\t\tif (!string.IsNullOrEmpty(" + PropCaseStartLower(mTableName.Replace("_", "")) + "Dal.GetErrorMessage()))");
                sql.AppendLine("\t\t\t\t{");
                sql.AppendLine("\t\t\t\t\tlog = new sysTLog();");
                sql.AppendLine("\t\t\t\t\tlog.logEvent = " + "\"" + "GetData " + PropCaseStartLower(mTableName).Replace("_", "") + " Error" + "\"" + ";");
                sql.AppendLine("\t\t\t\t\tlog.logMessage = " + PropCaseStartLower(mTableName.Replace("_", "")) + "Dal.GetErrorMessage();");
                sql.AppendLine("\t\t\t\t}");
                sql.AppendLine("\t\t\t\telse");
                sql.AppendLine("\t\t\t\t{");
                sql.AppendLine("\t\t\t\t\tlog = new sysTLog();");
                sql.AppendLine("\t\t\t\t\tlog.logEvent = " + "\"" + "View Data " + PropCaseStartLower(mTableName).Replace("_", "") + " Success" + "\"" + ";");
                sql.AppendLine("\t\t\t\t\tlog.logMessage = string.Empty;");
                sql.AppendLine("\t\t\t}");
                sql.AppendLine("\t\t\t\tlog.logSource = " + "\"" + PropCaseStartLower(mTableName) + "\"" + ";");
                sql.AppendLine("\t\t\t\tlog.createdBy = base.UserLoggedIn;");
                sql.AppendLine("\t\t\t\tlog.createdDate = DateTime.Now;");
                sql.AppendLine("\t\t\t\tlogDal.Save(log);");
                sql.AppendLine("\t\t\t\t// send params into jqGrid's format");
                sql.AppendLine("\t\t\t\tvar gridData = new");
                sql.AppendLine("\t\t\t{");
                sql.AppendLine("\t\t\t\tpage = page,");
                sql.AppendLine("\t\t\t\ttotal = totalPages,");
                sql.AppendLine("\t\t\t\trecords = totalCount,");
                sql.AppendLine("\t\t\t\trows = pagedList,");
                sql.AppendLine("\t\t\t};");
                sql.AppendLine("\t\t\t\treturn Newtonsoft.Json.JsonConvert.SerializeObject(gridData);");
                sql.AppendLine("\t\t}\t");
            }

            catch
            { }



            return sql.ToString();
        }

        private SqlCommand GetCommand(string sql)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            return cmd;
        }


        private string GetPK(string table)
        {

            string pk = "";
            DataTable pkTable = new DataTable();
            string sql = @"SELECT KCU.COLUMN_NAME 
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
        JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
        ON KCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME
        WHERE TC.CONSTRAINT_TYPE='PRIMARY KEY'
		AND KCU.TABLE_NAME=@tableName";

            SqlCommand cmd = GetCommand(sql);
            cmd.Parameters.AddWithValue("@tableName", table);
            object result = cmd.ExecuteScalar();
            cmd.Dispose();
            if (result != null)
                pk = result.ToString();

            return pk;
        }

        #endregion


    }
}
